<!DOCTYPE HTML>
<html>

<head>
	<link rel="bookmark"  type="image/x-icon"  href="/img/logo.png"/>
	<link rel="shortcut icon" href="/img/logo.png">
	
			    <title>
    Archer`s
    </title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" />
    <link rel="stylesheet" href="/css/mic_main.css" />
    <link rel="stylesheet" href="/css/dropdownMenu.css" />
    <meta name="keywords" content="Archer,arche2r,Arche2r,Archer的博客" />
    
    	<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
	 
    <noscript>
        <link rel="stylesheet" href="/css/noscript.css" />
    </noscript>
    <style type="text/css">
        body:before {
          content: ' ';
          position: fixed;
          top: 0;
          background: url('/img/bg.jpg') center 0 no-repeat;
          right: 0;
          bottom: 0;
          left: 0;
          background-size: cover; 
        }
    </style>

			    
  
    <script type="text/x-mathjax-config">
      MathJax.Hub.Config({
        tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
        }
      });
    </script>

    <script type="text/x-mathjax-config">
      MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for (i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
        }
      });
    </script>
    <script async type="text/javascript" src="//cdn.bootcss.com/mathjax/2.7.1/latest.js?config=TeX-AMS-MML_HTMLorMML"></script>
  


    <script src="/js/jquery.min.js"></script>
    <script src="/js/jquery.scrollex.min.js"></script>
    <script src="/js/jquery.scrolly.min.js"></script>
    <script src="/js/skel.min.js"></script>
    <script src="/js/util.js"></script>
    <script src="/js/main.js"></script>
	
<meta name="generator" content="Hexo 4.2.0"></head>
    
		
<!-- Layouts -->



<!--  代码渲染  -->
<link rel="stylesheet" href="/css/prism_coy.css" />
<link rel="stylesheet" href="/css/typo.css" />
<!-- 文章页 -->
<body class="is-loading">
    <!-- Wrapper 外包 s-->
    <div id="wrapper" class="fade-in">
        <!-- Intro 头部显示 s -->
        <!-- Intro 头部显示 e -->
        <!-- Header 头部logo start -->
        <header id="header">
    <a href="/" class="logo">IT`S ME</a>
</header>
        <!-- Nav 导航条 start -->
        <nav id="nav" class="special" >
            <ul class="menu links" >
			<!-- Homepage  主页  --> 
			<li >
	            <a href="/" rel="nofollow">主页</a>
	        </li>
			<!-- categories_name  分类   --> 
	        
	        <!-- archives  归档   --> 
	        
	        <li class="active">
	            <a href="#s1">归档</a>
	                    <ul class="submenu">
	                        <li>
	                        <a class="archive-link" href="/archives/2022/01/">一月 2022</a></li><li><a class="archive-link" href="/archives/2020/06/">六月 2020</a></li><li><a class="archive-link" href="/archives/2019/08/">八月 2019</a></li><li><a class="archive-link" href="/archives/2019/06/">六月 2019</a></li><li><a class="archive-link" href="/archives/2019/05/">五月 2019</a>
	                    </ul>
	        </li>
	        
	        
		        <!-- Pages 自定义   -->
		        
		        <li>
		            <a href="/gallery/" title="图库">
		                图库
		            </a>
		        </li>
		        


            </ul>
            <!-- icons 图标   -->
			<ul class="icons">
                    
                    <li>
                        <a title="tv" href="https://space.bilibili.com/39520610" target="_blank" rel="noopener">
                            <i class="icon fa fa-tv"></i>
                        </a>
                    </li>
                    
                    <li>
                        <a title="github" href="https://github.com/arche2r" target="_blank" rel="noopener">
                            <i class="icon fa fa-github"></i>
                        </a>
                    </li>
                    
                    <li>
                        <a title="qq" href="tencent://message/?uin=1151147869&Site=&Menu=yes" target="_blank" rel="noopener">
                            <i class="icon fa fa-qq"></i>
                        </a>
                    </li>
                    
			</ul>
</nav>

        <div id="main" >
            <div class ="post_page_title_img" style="height: 25rem;background-image: url(/images/pic/20200606/20200606.jpg);background-position: center; background-repeat:no-repeat; background-size:cover;-moz-background-size:cover;overflow:hidden;" >
                <a href="#" style="padding: 4rem 4rem 2rem 4rem ;"><h2 >分析函数OVER(PARTITION BY... ORDER BY...)</h2></a>
            </div>
            <!-- Post -->
            <div class="typo" style="padding: 3rem;">
                <p>事情发生在改造mysql到达梦数据库的过程中</p>
<p>源代码如下</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">      order_id,</span><br><span class="line">      substring_index(<span class="keyword">group_concat</span>(auditor_id <span class="keyword">order</span> <span class="keyword">by</span> audit_level <span class="keyword">asc</span> SEPARATOR <span class="string">';'</span>), <span class="string">';'</span>,<span class="number">1</span>) <span class="keyword">as</span> auditor_id,</span><br><span class="line">      substring_index(<span class="keyword">group_concat</span>(audit_level <span class="keyword">order</span> <span class="keyword">by</span> audit_level <span class="keyword">asc</span> SEPARATOR <span class="string">';'</span>), <span class="string">';'</span>,<span class="number">1</span>) <span class="keyword">as</span> audit_level</span><br><span class="line">      <span class="keyword">from</span> <span class="string">`vim-dpoints-trade`</span>.order_audit_result</span><br><span class="line">      <span class="keyword">where</span> audit_status <span class="keyword">is</span> <span class="literal">null</span></span><br><span class="line">      <span class="keyword">group</span> <span class="keyword">by</span> order_id</span><br></pre></td></tr></table></figure>

<p>这种在分组查询中嵌套使用聚合函数的sql在达梦中不被支持,所以需要开动小脑筋进行分析,该怎么把它变成普通嵌套sql?</p>
<p>然后它就变成了这样</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> tt.order_id,</span><br><span class="line">	</span><br><span class="line"> (<span class="keyword">select</span> auditor_id <span class="keyword">from</span> <span class="string">`vim-dpoints-trade`</span>.order_audit_result t </span><br><span class="line"> <span class="keyword">where</span> t.order_id = tt.order_id  <span class="keyword">and</span> audit_status <span class="keyword">is</span> <span class="literal">null</span>   <span class="keyword">order</span> <span class="keyword">by</span> audit_level <span class="keyword">asc</span> <span class="keyword">limit</span> <span class="number">1</span>  ) <span class="keyword">as</span> auditor_id,</span><br><span class="line"></span><br><span class="line"> (<span class="keyword">select</span> audit_level <span class="keyword">from</span> <span class="string">`vim-dpoints-trade`</span>.order_audit_result t </span><br><span class="line"><span class="keyword">where</span> t.order_id = tt.order_id <span class="keyword">and</span> audit_status <span class="keyword">is</span> <span class="literal">null</span> <span class="keyword">order</span> <span class="keyword">by</span> audit_level <span class="keyword">asc</span> <span class="keyword">limit</span> <span class="number">1</span>  ) <span class="keyword">as</span> audit_level</span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> <span class="string">`vim-dpoints-trade`</span>.order_audit_result tt </span><br><span class="line"><span class="keyword">where</span> audit_status <span class="keyword">is</span> <span class="literal">null</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> order_id</span><br></pre></td></tr></table></figure>

<p>上面这个sql就比较简单了 .</p>
<p>​    首先在最外层的sql中使用groupby进行分组查询,针对order_id进行分组后,在子查询中保持和外层查询使用相同的条件</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">t.order_id = tt.order_id  and audit_status is null</span><br></pre></td></tr></table></figure>

<p>然后按照原来的条件对audit_level进行升序排序,取出其中第一条数据作为查询结果.圆满完成!</p>
<p>在询问大姐大是否能替换原sql时,大姐大说 </p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">怎么不用over order by 呀? 这么经典的聚合查询?</span><br></pre></td></tr></table></figure>

<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">啥?这是啥玩意?</span><br></pre></td></tr></table></figure>

<p>来 ,这样写:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">        order_id  ,</span><br><span class="line">        auditor_id,</span><br><span class="line">        audit_level</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">        (<span class="keyword">select</span></span><br><span class="line">               order_id   ,</span><br><span class="line">               auditor_id ,</span><br><span class="line">               audit_level,</span><br><span class="line">               row_number() <span class="keyword">OVER</span> (<span class="keyword">partition</span> <span class="keyword">by</span> order_id <span class="keyword">order</span> <span class="keyword">by</span> audit_level <span class="keyword">asc</span>) rn1</span><br><span class="line">           <span class="keyword">from</span></span><br><span class="line">                        TRADE_ORDER_AUDIT_RESULT_T</span><br><span class="line">           <span class="keyword">where</span></span><br><span class="line">                        audit_status <span class="keyword">is</span> <span class="literal">null</span>)  t</span><br><span class="line"><span class="keyword">where</span>  t.rn1 = <span class="number">1</span></span><br></pre></td></tr></table></figure>

<p>简简单单,条理清晰.</p>
<p>上面这个和我拆分出来的sql一样,over <strong>A</strong> order by <strong>B</strong> 也是先对查询结果进行A为条件的分组,然后根据每组中的audit_level进行排序.查询出来的排序结果可以用row_number()进行显式的展示.</p>
<p>最后在外层针对row_number进行筛选就可以得到结果了.</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">嗯.又获得了了不起的知识呢~</span><br></pre></td></tr></table></figure>


            </div>

            <!-- Post Comments -->
            

        </div>
        <!-- Copyright 版权 start -->
                <div id="copyright">
                <span id="busuanzi_container_site_pv"><span id="busuanzi_value_site_pv"></span>次的回眸</span>
<span>·</span>
				<span id="busuanzi_container_site_pv"> 2022 </span> 

        </div>
    </div>
</body>



 	
</html>
